#!/bin/bash
# sh tranmysql2postgres.sh nanshan false true : 只同步结构
project_name=$1
syn_all=$2
only_schema=$3
# project_name=shuiliandong
# syn_all=false
drop_first=false # don't set develop environments ; please set true when run system-init scripts
##########################configuration##########################
operator_server_source_ip="172.16.1.119"
operator_server_source_user_name="yapai"
operator_server_source_password="ypkj$%^7"
operator_server_target_ip="172.16.1.119"
operator_server_target_user_name="postgres"
operator_server_target_password="postgres"
base_template_sql_folder="${HOME}/temp/yapai/system-init"
base_folder="${HOME}/temp/mysql"
trans_middle_database="test"
##########################configuration##########################
base_export_sql_folder="${base_folder}/export"
base_init_other_sql="${base_folder}/addtion"
update_table_desc_folder="${base_folder}/ddl"
export PGPASSWORD=${operator_server_target_password}
# start export workflow
mysql_template_files=(${project_name})
if [[ "${syn_all}" == "true" ]]
then
  mysql_template_files=`ls -1 ${base_template_sql_folder}/10_db_mysql`
fi
for template_file in ${mysql_template_files[*]}
do
  project_name=${template_file%.sql}
  mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'show databases' | grep -E '^'$project_name'$'
  import_data=false
  if [[ $? -eq 1 ]]
  then
    echo "no exit database "
    import_data=true
  elif [[ "${drop_first}" == "true" ]] 
  then
    mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'drop database '"${project_name}"''
    import_data=true
  else
    import_data=false
  fi
  if [[ "${import_data}" == "true" ]]
  then
    mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'create database '"${project_name}"''
    mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -D${project_name} < ${base_template_sql_folder}/10_db_mysql/${project_name}.sql 
  fi


  UPDATE_MYSQL_SQL='ALTER TABLE {{project_name}}.{{table_name}} MODIFY COLUMN {{field_name}} {{type_name}} NOT NULL COMMENT "{{comment_name}}";'

  # check type of tiyint  
  table_names=`mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'use '"${project_name}"';show tables' | sed -n '2,$p'`
  for table_name in ${table_names[@]}
  do
    tinyint_filed=`mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'use '"${project_name}"';desc '"${table_name}"'' | grep 'tinyint(1)' | awk '{print $1}'`
    if [[ -z ${tinyint_filed} ]]
    then
      continue
    fi

    for field_name in ${tinyint_filed[@]}
    do
      # TODO
      filed_result=`mysql -h ${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e "select  t.COLUMN_COMMENT  from information_schema.COLUMNS t where t.TABLE_NAME='${table_name}' and t.COLUMN_NAME ='${field_name}'" | sed -n '$p'`
      array=($filed_result) 
      real_sql=`echo ${UPDATE_MYSQL_SQL} | sed -n 's/{{project_name}}/'"${project_name}"'/g;p' | sed -n 's/{{table_name}}/'"${table_name}"'/g;p' | sed -n 's/{{field_name}}/'"${field_name}"'/g;p' | sed -n 's/{{type_name}}/int(4)/g;p' | sed -n 's/{{comment_name}}/'"${filed_result}"'/g;p'`
      mysql -h ${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e "${real_sql}"
    done
  done
  if [[ $only_schema == "false" ]]
  then
    psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -dpostgres -c "select nspname from pg_namespace" | grep ${project_name}
    if [[ $? -eq 1 ]]
    then
      echo "will create postgres schema...."
    else
      psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -dpostgres -c "drop schema ${project_name} cascade"
    fi
    docker run --rm -it dimitri/pgloader:ccl.latest pgloader  mysql://${operator_server_source_user_name}:${operator_server_source_password}@${operator_server_source_ip}:3306/${project_name} postgresql://${operator_server_target_user_name}:${operator_server_target_password}@${operator_server_target_ip}:5432/postgres
  else
    docker run --rm -it dimitri/pgloader:ccl.latest pgloader --with "include no drop" mysql://${operator_server_source_user_name}:${operator_server_source_password}@${operator_server_source_ip}:3306/${project_name} postgresql://${operator_server_target_user_name}:${operator_server_target_password}@${operator_server_target_ip}:5432/postgres
  fi
  if [[ ! -d ${base_export_sql_folder} ]]
  then
    mkdir -p ${base_export_sql_folder}
  fi

  if [[ -f ${base_export_sql_folder}/${project_name}.sql ]]
  then
    rm -rf ${base_export_sql_folder}/${project_name}.sql
  fi
 # whole functino ddlsql import 
  if [[ -d ${update_table_desc_folder}/whole ]]
  then
    whole_ddl_sql=`ls -1 ${update_table_desc_folder}/whole`
    for whole_item_sql in ${whole_ddl_sql[@]}
    do
      echo ${whole_item_sql}
      psql -h${operator_server_target_ip} -U${operator_server_target_user_name} -dpostgres -c 'set search_path to '"${project_name}"' ;' -a -f ${update_table_desc_folder}/whole/${whole_item_sql}
    done
  fi
  
  if [[ -d ${update_table_desc_folder}/${project_name} ]]
  then
    ddl_sql=`ls -1 ${update_table_desc_folder}/${project_name}`
    for item_sql in ${ddl_sql[@]}
    do
      echo ${item_sql}
      psql -h${operator_server_target_ip} -U${operator_server_target_user_name} -dpostgres -c 'set search_path to '"${project_name}"' ;' -a -f ${update_table_desc_folder}/${project_name}/${item_sql}
    done
  fi
  # export
  PGPASSWORD=${operator_server_target_password} docker run --rm -it postgres:14.2 pg_dump -h${operator_server_target_ip} -U${operator_server_target_user_name} -d postgres --schema=${project_name} > ${base_export_sql_folder}/${project_name}.sql

  if [[ -f ${HOME}/temp/mysql/addtion/${project_name}.sql ]]
  then
    cat ${base_init_other_sql}/${project_name}.sql >> ${base_export_sql_folder}/${project_name}.sql
  fi
  # list all views
  view_list=`mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'select TABLE_NAME  from information_schema.VIEWS v where TABLE_SCHEMA ="'"${project_name}"'"\G' | grep -E 'TABLE_NAME:' | awk -F ':' '{print $2}'`
  for view_item in ${view_list[*]}
  do
    # mysql -h127.0.0.1 -uroot -p123456 -e 'use shuiliandong;show create view '"${view_item}"'\G' | grep -E 'Create View' | awk -F ':' '{print $2}' | sed 's/`//g'>>${base_export_sql_folder}/${project_name}.sql
    echo $view_item
    mysql -h${operator_server_source_ip} -u${operator_server_source_user_name} -p${operator_server_source_password} -e 'use '"${project_name}"';show create view '"${view_item}"'\G' | grep -E 'Create View' | awk -F ':' '{print $2}' | sed 's/`//g' | sed 's/CREATE.*VIEW/CREATE OR REPLACE VIEW/g' | sed 's/$/;/' | sed 's/^/set search_path to '"${project_name}"';/g'>>${base_export_sql_folder}/${project_name}.sql
  done

  psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -c "select datname from pg_database" | grep ${trans_middle_database}
  if [[ $? -eq 1 ]]
  then
    psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -c "create database ${trans_middle_database}" 
  fi
  psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -d${trans_middle_database} -c "select nspname from pg_namespace" | grep ${project_name}
  if [[ $? -eq 1 ]]
  then
    echo "will create postgres schema...."
  else
    psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -d${trans_middle_database} -c "drop schema ${project_name} cascade"
  fi
  psql -h ${operator_server_target_ip} -U${operator_server_target_user_name} -d${trans_middle_database} -c 'set search_path to '"${project_name}"'' -f ${base_export_sql_folder}/${project_name}.sql
done
